package br.projecao.laboratorio.dao;

import br.projecao.laboratorio.negocio.Horario;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Desenvolvimento
 */
public class HorarioDAO extends DAO {

    public HorarioDAO() {
    }

    public void cadastraHora(Horario horario) throws SQLException {
        StringBuilder inserir = new StringBuilder("INSERT INTO lab_horario (matricula,dia,horario_inicio,horario_fim)");
        inserir.append(" value (?,?,?,?) ");
        try {
            abreConexao();
            PreparedStatement pst = conexao.prepareStatement(inserir.toString());
            pst.setInt(1, horario.getMatricula());
            pst.setInt(2, horario.getDia());
            pst.setString(3, horario.getHorarioInicio());
            pst.setString(4, horario.getHorarioFim());
            pst.execute();
        } catch (SQLException e) {
            throw e;
        } finally {
            fechaConexao();
        }
    }

    public void alterar(Horario h) throws SQLException {

        StringBuilder alt = new StringBuilder("UPDATE lab_horario set ");
        alt.append(" dia=?,");
        alt.append(" horario_inicio=?,");
        alt.append(" horario_fim=?,");
        alt.append(" WHERE idhorario=? ");

        try {
            abreConexao();
            PreparedStatement pst = conexao.prepareStatement(alt.toString());
            pst.setInt(1, h.getDia());
            pst.setString(2, h.getHorarioInicio());
            pst.setString(3, h.getHorarioFim());
            pst.setInt(4, h.getIdhorario());
            pst.execute();
        } catch (SQLException ex) {
            throw ex;
        } finally {
            fechaConexao();
        }
    }

    public void excluir(Horario h) throws SQLException {
        StringBuilder del = new StringBuilder(" DELETE FROM lab_horario ");
        del.append(" WHERE idhorario=? ");
        try {
            abreConexao();
            PreparedStatement pst = conexao.prepareStatement(del.toString());
            pst.setInt(1, h.getIdhorario());
            pst.execute();
        } catch (SQLException ex) {
            throw ex;
        } finally {
            fechaConexao();
        }
    }

    public ArrayList<Horario> consulta(Horario horario) throws SQLException {
        ArrayList<Horario> h = new ArrayList<Horario>();
        StringBuilder sql = new StringBuilder("select * from lab_horario ");
        sql.append("where matricula like?");
        try {
            abreConexao();
            PreparedStatement pst = conexao.prepareStatement(sql.toString());
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                horario = new Horario();
                horario.setMatricula(rs.getInt("matricula"));
                horario.setDia(rs.getInt("dia"));
                horario.setHorarioInicio(rs.getString("horaio_inicio"));
                horario.setHorarioFim(rs.getString("horaio_fim"));
                h.add(horario);
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            fechaConexao();
        }
        return h;
    }

    public ArrayList<Horario> consultaHorario(Horario horario) throws SQLException {
        ArrayList<Horario> retorno = new ArrayList<Horario>();
        if (horario.getMatricula() == null) {
            return retorno;
        }
        if (horario.getIdhorario() != null) {
            retorno.add(consultaHorarioPorCodigo(horario));
            return retorno;
        }
        StringBuilder sql = new StringBuilder("select * from lab_horario ");
        sql.append(" where matricula = ? ");
        try {
            abreConexao();
            PreparedStatement pst = conexao.prepareStatement(sql.toString());


            pst.setInt(1, horario.getMatricula());
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                horario = new Horario();
                horario.setIdhorario(rs.getInt("idhorario"));
                horario.setMatricula(rs.getInt("matricula"));
                horario.setDia(rs.getInt("dia"));
                horario.setHorarioInicio(rs.getString("horario_inicio"));
                horario.setHorarioFim(rs.getString("horario_fim"));
                retorno.add(horario);
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            fechaConexao();
        }
        return retorno;
    }

    public Horario consultaHorarioPorCodigo(Horario horario) throws SQLException {
        StringBuilder sql = new StringBuilder("select * from lab_horario ");
        sql.append(" where idhorario=? ");
        try {
            abreConexao();
            PreparedStatement pst = conexao.prepareStatement(sql.toString());
            pst.setInt(1, horario.getIdhorario());
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                horario = new Horario();
                horario.setIdhorario(rs.getInt("idhorario"));
                horario.setMatricula(rs.getInt("matricula"));
                horario.setDia(rs.getInt("dia"));
                horario.setHorarioInicio(rs.getString("horario_inicio"));
                horario.setHorarioFim(rs.getString("horario_fim"));
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            fechaConexao();
        }
        return horario;
    }
}
